var express = require('express');
var router = express.Router();

const MyQuery = require('../utils/db')

//引入配置文件
const { API_URL } = require('../utils/config')

router.get('/demo2', function(req, res, next) {
    res.send('列表模块')
});

//热门城市数据接口
router.get('/areas', async(req, res, next) => {

    let {} = req.query

    let data = {} //最终要返回的数据格式  msg,status,result
        //1. 写sql
    let sql = `select * from e_area `
        //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
})

//省略几个接口，自己cv
//按年级选择接口
router.get('/grades', async(req, res, next) => {

    let {} = req.query

    let data = {} //最终要返回的数据格式  msg,status,result
        //1. 写sql
    let sql = ` select * from e_grade  `
        //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
});


//按学科选择接口
router.get('/subjects', async(req, res, next) => {

    let {} = req.query

    let data = {} //最终要返回的数据格式  msg,status,result
        //1. 写sql
    let sql = `SELECT * FROM e_subject`
        //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
});


//按类别选择接口

router.get('/cates', async(req, res, next) => {

    let {} = req.query

    let data = {} //最终要返回的数据格式  msg,status,result
        //1. 写sql
    let sql = `select * from e_category`
        //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
});



//课程列表接口
router.get('/courses', async(req, res, next) => {
    // let page = 2//要获取第几页的内容
    // let pageSize = 5//每页展示size条
    let { page = 1, pageSize = 5, area = '', grade = '', category = '', subject = '' } = req.query
    let data = {} //最终要返回的数据格式  msg,status,result

    // 1)数据处理
    page = page - 0 //parseInt / Number
    pageSize = pageSize - 0 //parseInt / Number
        // 2) 数据严格性
    if (!page || page <= 0) { //如果报错给默认值  
        page = 1
    }
    if (!pageSize || pageSize <= 0) { //如果报错给默认值
        pageSize = 5
    }

    //分析分页
    let start = (page - 1) * pageSize //从第start条开始取

    /*
        条件筛选一共分3种情况：
            1）没有条件，无WHERE
            2）有一个条件，有WHERE无AND
            3）两个及以上，有WHERE 有 AND 
        let whereStr = ``
        let whereStr = `WHERE grade_name = '高一' `
        let whereStr = `WHERE grade_name = '高一' AND area_name = '北京'`
    */
    let whereStr = `` //默认什么条件也没有

    //  console.log(``=='');

    //let area = '河北' //如果area变量不为空，则代表要以地区进行筛选
    if (area != '') {
        if (whereStr == '') {
            whereStr += `WHERE area_name = '${area}'`
        } else {
            whereStr += ` AND area_name = '${area}'`
        }
    }

    //let grade = '初一' //如果grade变量不为空，则代表要以年级进行筛选
    if (grade != '') {
        if (whereStr == '') {
            whereStr += `WHERE grade_name = '${grade}'`
        } else {
            whereStr += ` AND grade_name = '${grade}'`
        }
    }

    if (subject != '') {
        if (whereStr == '') {
            whereStr += `WHERE subject_name = '${subject}'`
        } else {
            whereStr += ` AND subject_name = '${subject}'`
        }
    }

    if (category != '') {
        if (whereStr == '') {
            whereStr += `WHERE category_name = '${category}'`
        } else {
            whereStr += ` AND category_name = '${category}'`
        }
    }

    //1. 写sql1,有limit的
    let sql1 = ` SELECT *,CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src,concat('${API_URL}/uploads/courses/',detail_imgs) as detail_imgs  FROM e_course ${whereStr} LIMIT ${start},${pageSize} `
        // let sql2 = ` SELECT * FROM e_course ${whereStr}`

    let sql2 = ` SELECT count(*) as row FROM e_course ${whereStr}`;

    //2. 执行sql
    let { result: result1 } = await MyQuery(sql1) //{ err,result }
    let { result: result2 } = await MyQuery(sql2) //{ err,result }
        // let rows = result2.length //总条数

    let rows = result2[0].row //总条数

    let totalPage = Math.ceil(rows / pageSize) //总页码
        // res.send( result2.length + '' )
        // return
        //3. 要处理成规范、格式化的json数据
        //做分页
    data = {
        msg: 'OK',
        status: 200,
        result: {
            data: result1,
            page, //当前页码
            totalPage, //总页码
            rows, //总条数
        }
    }
    res.send(data)
});


//列表-搜索接口
router.get('/search', async(req, res, next) => {
    let { page = 1, pageSize = 5, keyword = '' } = req.query
    let data = {} //最终要返回的数据格式  msg,status,result

    // if (keyword == '') {
    //     data = {
    //         msg: '请输入关键字',
    //         status: 500
    //     }
    //     res.send(data)
    //     return
    // }

    // 1)数据处理
    page = page - 0 //parseInt / Number
    pageSize = pageSize - 0 //parseInt / Number
        // 2) 数据严格性
    if (!page || page <= 0) { //如果报错给默认值  
        page = 1
    }
    if (!pageSize || pageSize <= 0) { //如果报错给默认值
        pageSize = 5
    }

    //分析分页
    let start = (page - 1) * pageSize //从第start条开始取

    let whereStr = `` //默认什么条件也没有

    if (keyword != '') {
        whereStr = `WHERE title like '%${keyword}%'`
    }


    //1. 写sql1,有limit的
    // select * from e_course where title like '%初一%'
    let sql1 = ` SELECT *,CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src ,concat('${API_URL}/uploads/courses/',detail_imgs) as detail_imgs FROM e_course ${whereStr} LIMIT ${start},${pageSize} `
        // let sql2 = ` SELECT * FROM e_course ${whereStr}`
    let sql2 = ` SELECT count(*) as row FROM e_course ${whereStr}`;
    //2. 执行sql
    let { result: result1 } = await MyQuery(sql1) //{ err,result }
    let { result: result2 } = await MyQuery(sql2) //{ err,result }
        // let rows = result2.length //总条数

    let rows = result2[0].row //总条数

    let totalPage = Math.ceil(rows / pageSize) //总页码
        // res.send( result2.length + '' )
        // return
        //3. 要处理成规范、格式化的json数据
        //做分页
    data = {
        msg: 'OK',
        status: 200,
        result: {

            page, //当前页码
            totalPage, //总页码
            rows, //总条数
            keyword,
            data: result1
        }
    }
    res.send(data)

});


module.exports = router;